A key to success in business today is to understand and effectively manage the factors that drive an enterprise—a field known as Business Intelligence (BI). Having critical Information about such business drivers allows decisions that will significantly improve results. As organizations ‘flatten’, that is, the number of levels in their hierarchies decrease, these mission-critical decisions are being made at lower levels—which means that almost every employee in an enterprise needs quick, easy access to appropriate information. In spite of this necessity, corporate information remains inaccessible for many employees—virtually locked away in data warehouses, data marts, enterprise resource planning systems, and myriad corporate databases.
Early BI systems provided valuable information from these data stores by retrieving transaction-level detail from On-line Transaction Processing (OLTP) databases. This capability relied on Information Technology (IT) specialists building complex queries using languages like SQL. The advent of On-Line Analytical Processing (OLAP), however, has given organizations more effective and meaningful access to critical corporate data. Modern OLAP systems consolidate and present summarized corporate information from a multitude of sources. This technology allows users to view information in a business context: sales per quarter per sales representative, units shipped on time per city per branch by air, and so on. By presenting corporate data so that it is related to the business structure, trends and anomalies can be more easily spotted and addressed.
OLAP reports—interactive reports that are highly formatted, easily deployed, and straightforward to use—deliver value to the entire organization. These reports accelerate the “Eureka moment” by exposing “sweet spots” of information in a data set directly to decision makers, knowledge workers, and information consumers. “Sweet spots” are selective pieces or collections of information that provide decision makers with immediate critical insight into business drivers. These OLAP reports can be regular status reports, but are especially effective for key performance indicator (KPI) reporting, business performance measurement reporting, and scorecard reporting, all of which are becoming increasingly important to decision makers. A robust reporting environment is required in which to perform these tasks.
Products such as PowerPlay™ by Cognos Inc. enable organizations to create and deploy highly formatted, interactive OLAP reports. These reports let users easily measure, manage, and track improvements in business performance. They also allow easy distribution of this information across the enterprise. Decision makers throughout the organization now have the information they need to significantly improve business results.
Because of the complexity of corporate organizations, and their data, it is not unusual for a particular enterprise to deploy several different products to analyze their data. Often these products are from different vendors.
For companies that want to track performance and trends, or perform scorecard-style management reporting (viz.: short, concise, and consistent), there is an even more fundamental concern. It can be extremely difficult to understand “the big picture” when the only accessible reports focus on transaction-level detail, because data in databases is organized for efficient storage and administration, not for summary-level analysis or exploration. In addition, data storage does not correspond to how the business is organized, so data must usually be manipulated and reformatted before the user can extract useful information from it.
If, for example, managers want to explore company performance in terms of product sales, a report that details the performance of individual sale reps will not help them spot overall trends. By reviewing summary information first, such as total sales per office r region, decision makers can more easily gain a “big picture” view of business performance. They can then drill down to lower-level details to uncover what is driving these trends. Thus OLAP technology has brought significant value to business decision making. OLAP systems store and access data as dimensions that represent business factors like time, products, geographical regions, and sales channels. This information is stored “multi-dimensionally”—like a cube that can be viewed, turned, and explored from any angle. The information is also presented in a business context, like ‘number of customer complaints by product line in North America last quarter,’ rather than a database context—so decision makers have immediate access to the information they need to make the best decisions for the business.
Until recently, organizations have found it difficult to meet some of these user requirements. However, with the advent of OLAP tools enterprise-wide deployment of OLAP reports is now a reality. Cubes can be customized to reflect the dimensions and calculations (also called measures) based on data stored in the original database most commonly used in a given organization. OLAP reports are generated from one or more of these data cubes. Because each cube contains a wide variety of dimensions and measures, a vast number of reports can be built from the information in the cubes. The cubes can be considered as master reports or a collection of components that can be assembled to create a specific report.
With OLAP reports, the users first view of the data is a ‘top-level’ one that reveals patterns and trends at a glance. If users have identified issues in this summary-level information, OLAP reports enable them to fully explore and analyze the data set from several perspectives or angles, to varying levels of detail. The reports also enable users to ‘slice and dice’, drill down, drill up, and provide alternative graphical views of their data—something paper reports cannot offer. (The term ‘slice and dice’ generally implies a systematic reduction of a body of data into smaller parts or views that will yield more information. The term is also used to mean the presentation of information in a variety of different and useful ways.)
OLAP reports that take an analyze-then-query approach allow decision makers to access data the same way they identify and solve problems: by reviewing totals or summary information first then looking at the underlying details by drilling down to transaction-level details whenever necessary.
There are two major stages in implementing a reporting solution. The first step is to create OLAP cubes, the multidimensional structures that house summary-level details of the corporate data. Typically, these cubes are created by IT specialists and deployed to information analysts and report authors. The cubes are customized models of a business that reflect the unique characteristics of the company. The structure of a cube is defined in terms of dimensions and measures. Dimensions are hierarchical categories of information like time, products, and geography. For example, the product dimension hierarchy may be organized by product line, product group, and then by individual product. Measures are the (results of) calculations based on the original data that are used to track the business such as revenue, units sold, and cost of sales. In other words, measures are the numeric columns that present the count or summation of particular values that users would like to see in their reports.
OLAP cubes generally contain only the dimensions and measures relevant to a specific analysis. For example, sales analysis data and human resources data would be housed in separate cubes. This ensures that cubes remain manageable, not just in terms of their size but also in terms of the clarity of the information they contain. With appropriate tools, diverse but compatible cubes can be easily linked together so that users can move effortlessly from one cube to another, accessing information from all areas of the company.
Once OLAP cubes are created and deployed, report authors have everything they need to produce a wealth of OLAP reports. The process for authoring is extremely straightforward for all types of reports; status reports that reveal a snapshot of data; ad hoc reports that answer specific questions; and business performance management reports that track KPIs (Key Performance Indicators).
Although OLAP reports can be distributed on paper, it is well known that decision makers reap the most value when the reports are presented electronically. There are typically three ways to explore data in an OLAP report:
Drill down/Drill up: Users can explore a dimension hierarchically moving from summary-level information to the details and back—to gain fast answers to critical business questions. A financial manager who is concerned with rising expenses will want to understand what parts of the company are particularly problematic, By drilling down on a geographical dimension, they can move from looking at expenses by country, by region, by office and then finally by department. Drilling up is the reverse process, so that the information becomes more summarized, and less detailed.
Drill-through and ‘Slice and dice’: Decision makers can interactively explore corporate data in any combination of dimensions, from many different angles or perspectives. For example, a sales manager can look at revenue figures by product line, sales region, time period, or sales channel.
Graphical analysis: Users can choose from a variety of graphical displays with which to depict the key factors that are driving the business and assist them in understanding the performance of various aspects of the business.
In typical BI products the information defining the mapping of source and target drill-through objects is spread over several report generating applications, sometimes obtained from different vendors, and uses data also provided by different products, sometimes also from different vendors. This makes administration difficult since changing drill-through behavior requires the administrator to apply the changes to more than one report generating application. It also means that it is very difficult to deploy or assess dependencies for drill-through installations.
Another problem is that drill-through definitions are either saved in cubes or saved in report definitions, so that when a change is made, such as a report being deleted or modified, it is very hard for the administrator to determine what cubes or other reports are dependent on the report that is being deleted or modified (i.e. are possible drill-through targets).
Further, drill-through objects are somewhat “closed in” or concealed, that is, unintentionally, but effectively, hidden from external applications, so that it is difficult for third party applications to fully utilize them. The fact that the data describing a particular drill-through (the drill-through metadata) might be distributed over several sources means that it is also difficult to integrate it with such third-party applications. Additionally, each of these applications likely has different data format requirements.